﻿using System;
using System.Collections.Generic;
using System.Text;
using Microsoft.Practices.EnterpriseLibrary.Data;
using System.Data.Common;
using NLBS.Entity;
using System.Data;

namespace NLBS.Data
{
    public partial class AccessDataProvider : DbProvider
    {
        #region Article

        public override IPagedCollection<Article> GetRssArticleByPage(int pageSize)
        {

            string strPageSQL = string.Format(@"select top {0} cat.*,cat.cat_name as cat_Name,art.* FROM blog_Article art
            left join blog_category cat on art.log_catid=cat.cat_id where log_id<=(select min(log_id) 
            from (select top {1} log_id from blog_Article order by log_id desc) as T)
            And log_enablerss=1  order by log_isTop desc,log_postTime desc;", pageSize, 10);


            DbCommand dbCommand = db.GetSqlStringCommand(strPageSQL);
            IPagedCollection<Article> lists = new PagedCollection<Article>();
            using (IDataReader dataReader = db.ExecuteReader(dbCommand))
            {
                while (dataReader.Read())
                {
                    Article article = CommonDataProvider.PopulateArticleFromIDataReader(dataReader);
                    article.Category = CommonDataProvider.PopulateCategoryFromIDataReader(dataReader);
                    lists.Add(article);
                }

                lists.MaxItems =ExecuteScalar("SELECT COUNT(log_id) AS TotalRecords from blog_Article where log_enablerss=1");
                return lists;
            }
        }

        public override IPagedCollection<Article> GetAllArticleByPage(int startIndex, int pageSize)
        {
            string strPageSQL = string.Format(@"select top {0} cat.*,cat.cat_name as cat_Name,art.* FROM blog_Article art
            left join blog_category cat on art.log_catid=cat.cat_id where log_id<=(select min(log_id) 
            from (select top {1} log_id from blog_Article order by log_id desc) as T) order by log_id desc;
            ", pageSize, startIndex);

            string strCountSQL = "SELECT COUNT(log_id) AS TotalRecords from blog_Article";
            
            DbCommand dbCommand = db.GetSqlStringCommand(strCountSQL);
            int count = (int)db.ExecuteScalar(dbCommand);
            
            IPagedCollection<Article> lists = new PagedCollection<Article>();
            lists.MaxItems = count;
            dbCommand = db.GetSqlStringCommand(strPageSQL);
            

            
            using (IDataReader dataReader = db.ExecuteReader(dbCommand))
            {
                while (dataReader.Read())
                {
                    Article article = CommonDataProvider.PopulateTopAndCommentArticleFromIDataReader(dataReader);
                    article.Category = CommonDataProvider.PopulateCategoryFromIDataReader(dataReader);
                    lists.Add(article);

                }
                return lists;
            }

        }

        public override IPagedCollection<Article> GetCatArticleByPage(int startIndex, int pageSize, int catID)
        {
            string strPageSQL = string.Format(@"select top {0} cat.*,cat.cat_name as cat_Name,art.* FROM blog_Article art
            left join blog_category cat on art.log_catid=cat.cat_id where log_id<=(select min(log_id) 
            from (select top {1} log_id from blog_Article where log_catID={2} order by log_id desc) as T) and log_catID={2}  order by log_id desc;
            ", pageSize, startIndex, catID);

            string strCountSQL = "SELECT COUNT(log_id) AS TotalRecords from blog_Article WHERE log_catID=?";
            DbCommand dbCommand = db.GetSqlStringCommand(strCountSQL);
            db.AddInParameter(dbCommand, "catID", DbType.Int32, catID);
            int count = (int)db.ExecuteScalar(dbCommand);
            IPagedCollection<Article> lists = new PagedCollection<Article>();
            lists.MaxItems = count;
            dbCommand = db.GetSqlStringCommand(strPageSQL);
            using (IDataReader dataReader = db.ExecuteReader(dbCommand))
            {
                while (dataReader.Read())
                {
                    Article article = CommonDataProvider.PopulateArticleFromIDataReader(dataReader);

                    lists.Add(article);
                }
                
                return lists;
            }

        }

        public override IPagedCollection<Article> GetSelectedArticleByPage(int startIndex, int pageSize)
        {

            string strPageSQL = string.Format(@"select top {0} cat.*,cat.cat_name as cat_Name,art.* FROM blog_Article art
            left join blog_category cat on art.log_catid=cat.cat_id where log_id<=(select min(log_id) 
            from (select top {1} log_id from blog_Article where  log_selected=1 order by log_id desc) as T) and log_selected=1 order by log_id desc;
            ", pageSize, startIndex);

            string strCountSQL = "SELECT COUNT(log_id) AS TotalRecords from blog_Article where log_selected=1";

            DbCommand dbCommand = db.GetSqlStringCommand(strCountSQL);
            int count = (int)db.ExecuteScalar(dbCommand);

            IPagedCollection<Article> lists = new PagedCollection<Article>();
            lists.MaxItems = count;
            dbCommand = db.GetSqlStringCommand(strPageSQL);
            using (IDataReader dataReader = db.ExecuteReader(dbCommand))
            {
                while (dataReader.Read())
                {
                    Article article = CommonDataProvider.PopulateTopAndCommentArticleFromIDataReader(dataReader);
                    article.Category = CommonDataProvider.PopulateCategoryFromIDataReader(dataReader);
                    lists.Add(article);

                }
                return lists;
            }

        }

        public override IList<Article> GetTopArticle(int intCount)
        {
            string sqlWHERE = string.Empty;
            if (intCount < 10)
            {
                intCount = 10;
            }
            foreach (Category item in this.GetCategory())
            {
                if (item.Hidden) sqlWHERE += " AND log_catid<>" + item.CategoryID;
            }
            List<string> intList = new List<string>();
            DbCommand dbHiddenCommand = db.GetSqlStringCommand("SELECT TOP " + intCount + " log_id FROM [blog_Article] WHERE log_mode=1 " + sqlWHERE + " ORDER BY log_postTime DESC");
            using (IDataReader dataReader = db.ExecuteReader(dbHiddenCommand))
            {
                while (dataReader.Read())
                {
                    //if (!intList.Contains(Convert.ToInt32(dataReader["log_id"])))
                    intList.Add(Convert.ToString(dataReader["log_id"]));
                }
            }
            DbCommand dbAllCommand = db.GetSqlStringCommand("SELECT TOP " + intCount + " log_id FROM [blog_Article] ORDER BY log_postTime DESC");
            using (IDataReader dataReader = db.ExecuteReader(dbAllCommand))
            {
                while (dataReader.Read())
                {
                    //if (!intList.Contains(Convert.ToInt32(dataReader["log_id"])))
                    intList.Add(Convert.ToString(dataReader["log_id"]));
                }
            }
            IList<Article> lists = new List<Article>();
            if (intList.Count == 0)
            {
                return lists;
            }

            intList.Sort();
            string arrItem = intList[0];
            for (int i = 1; i < intList.Count; i++)
            {
                if (arrItem == intList[i])
                {
                    intList.RemoveAt(i);
                    i--;
                }
                else
                {
                    arrItem = intList[i];
                }
            }

            DbCommand dbCommand = db.GetSqlStringCommand(string.Format(@"SELECT blog_Article.*,blog_Category.* FROM 
            blog_Article INNER JOIN blog_Category ON 
            blog_Article.log_catID = blog_Category.cat_id 
            where blog_Article.log_id in ({0})  ORDER BY blog_Article.log_postTime DESC", String.Join(",", intList.ToArray())));
            using (IDataReader dataReader = db.ExecuteReader(dbCommand))
            {
                while (dataReader.Read())
                {
                    Article article = CommonDataProvider.PopulateNoCatArticleFromIDataReader(dataReader);
                    article.Category = CommonDataProvider.PopulateCategoryFromIDataReader(dataReader);
                    lists.Add(article);
                }
            }
            return lists;
        }

        public override IList<Article> GetArticle()
        {
            
            DbCommand dbCommand = db.GetSqlStringCommand("select * from blog_article");
            IList<Article> lists = new List<Article>();
            using (IDataReader dataReader = db.ExecuteReader(dbCommand))
            {
                while (dataReader.Read())
                {
                    Article article = CommonDataProvider.PopulateNoCatArticleFromIDataReader(dataReader);
                    lists.Add(article);
                }
            }
            return lists;
        }

        public override Article GetArticleByID(int articleID)
        {
            
            DbCommand dbCommand = db.GetSqlStringCommand(@"SELECT   cat.*,cat.cat_name as cat_Name,log_id, log_catID, 
                log_title, log_authorID, log_author, log_editMark, log_trackbackURL, log_content0, log_content1, 
                log_mode, log_locked, log_selected, log_ubbFlags, log_postTime, log_ip, log_commentCount, log_viewCount, 
                log_trackbackCount,log_istop,log_enablerss
                FROM      blog_Article Article left join blog_category cat on Article.log_catid=cat.cat_id
                WHERE   (log_id =?)");
            db.AddInParameter(dbCommand, "articleID", DbType.Int32, articleID);
            IDataReader dr = db.ExecuteReader(dbCommand);
            Article article = null;
            if (dr.Read())
            {
                article = CommonDataProvider.PopulateTopAndCommentArticleFromIDataReader(dr);
            }
            return article;
        }

        public override int InsertArticle(Article item)
        {

            

            using (DbConnection connection = db.CreateConnection())
            {
                connection.Open();
                DbTransaction transaction = connection.BeginTransaction();
                try
                {

                    DbCommand dbCommand = db.GetSqlStringCommand(@"INSERT INTO [blog_Article] 
              ([log_catID], [log_title], [log_authorID], [log_author], [log_editMark],
              [log_trackbackURL], [log_content0], [log_content1], [log_mode], [log_locked],
              [log_selected], [log_ubbFlags], [log_postTime], [log_ip], 
              [log_commentCount], [log_viewCount], [log_trackbackCount],log_istop,log_enableRss)
              VALUES 
              (?, ?, ?, ?, ?, 
              ?, ?, ?, ?, ?,
              ?, ?, ?,?, 
              ?, ?, ?,?,?)");

                    db.AddInParameter(dbCommand, "catID", DbType.Int32, item.CategoryID);
                    db.AddInParameter(dbCommand, "title", DbType.String, item.Title);
                    db.AddInParameter(dbCommand, "authorID", DbType.Int32, item.AuthorID);
                    db.AddInParameter(dbCommand, "author", DbType.String, item.Author);
                    db.AddInParameter(dbCommand, "editMark", DbType.String, item.EditMark);

                    db.AddInParameter(dbCommand, "trackbackURL", DbType.String, item.TrackbackURL);
                    db.AddInParameter(dbCommand, "content0", DbType.String, item.Content0);
                    db.AddInParameter(dbCommand, "content1", DbType.String, item.Content1);
                    db.AddInParameter(dbCommand, "mode", DbType.Int16, item.Mode);
                    db.AddInParameter(dbCommand, "locked", DbType.Boolean, item.Locked);

                    db.AddInParameter(dbCommand, "selected", DbType.Boolean, item.Selected);
                    db.AddInParameter(dbCommand, "ubbFlags", DbType.String, item.UbbFlags);
                    db.AddInParameter(dbCommand, "postTime", DbType.String, item.PostTime.ToString());
                    db.AddInParameter(dbCommand, "ip", DbType.String, item.IP);
                    db.AddInParameter(dbCommand, "commentCount", DbType.Int32, item.CommentCount);
                    db.AddInParameter(dbCommand, "viewCount", DbType.Int32, item.ViewCount);
                    db.AddInParameter(dbCommand, "trackbackCount", DbType.Int32, item.TrackBackCount);
                    db.AddInParameter(dbCommand, "istop", DbType.Boolean, item.IsTop);
                    db.AddInParameter(dbCommand, "enableInRss", DbType.Boolean, item.EnableInRss);
                    db.ExecuteNonQuery(dbCommand, transaction);
                    dbCommand.Parameters.Clear();
                    dbCommand.CommandText = "SELECT @@IDENTITY";
                    int result = (int)db.ExecuteScalar(dbCommand, transaction);
                    transaction.Commit();
                    return result;
                }
                catch (Exception e)
                {
                    transaction.Rollback();
                    throw e;
                }
                finally
                {
                    connection.Close();
                }
                
            }
            
            return 0;
        }

        public override void UpdateArticle(Article item)
        {
            
            DbCommand dbCommand = db.GetSqlStringCommand(@"UPDATE [blog_Article] 
            SET [log_catID] = ?, [log_title] = ?, [log_authorID] = ?, [log_author] = ?, 
            [log_editMark] = ?, [log_trackbackURL] = ?, [log_content0] = ?, 
            [log_content1] = ?, [log_mode] = ?, [log_locked] = ?, [log_selected] = ?, 
            [log_ubbFlags] = ?, [log_postTime] = ?, [log_ip] = ?,log_istop=?,log_enableRss=?
            WHERE (([log_id] = ?))");
            
            db.AddInParameter(dbCommand, "catID", DbType.Int32, item.CategoryID);
            db.AddInParameter(dbCommand, "title", DbType.String, item.Title);
            db.AddInParameter(dbCommand, "authorID", DbType.Int32, item.ArticleID);
            db.AddInParameter(dbCommand, "author", DbType.String, item.Author);
            db.AddInParameter(dbCommand, "editMark", DbType.String, item.EditMark);

            db.AddInParameter(dbCommand, "trackbackURL", DbType.String, item.TrackbackURL);
            db.AddInParameter(dbCommand, "content0", DbType.String, item.Content0);
            db.AddInParameter(dbCommand, "content1", DbType.String, item.Content1);
            db.AddInParameter(dbCommand, "mode", DbType.Int32, item.Mode);
            db.AddInParameter(dbCommand, "locked", DbType.Boolean, item.Locked);

            db.AddInParameter(dbCommand, "selected", DbType.Boolean, item.Selected);
            db.AddInParameter(dbCommand, "ubbFlags", DbType.String, item.UbbFlags);
            db.AddInParameter(dbCommand, "postTime", DbType.DateTime, item.PostTime);
            db.AddInParameter(dbCommand, "ip", DbType.String, item.IP);
            db.AddInParameter(dbCommand, "istop", DbType.Boolean, item.IsTop);
            db.AddInParameter(dbCommand, "enableInRss", DbType.Boolean, item.EnableInRss);
            db.AddInParameter(dbCommand, "articleID", DbType.Int32, item.ArticleID);
            db.ExecuteNonQuery(dbCommand);
            UpdateArticleCount(item.ArticleID,item.CategoryID);
        }

        public override void UpdateArticleCount(int oldCatID, int catID, int authorID)
        {
            
            if (oldCatID != catID)
            {
                int oldcatCounter = ExecuteScalar(string.Format("select count(log_id) from blog_Article WHERE log_catid={0}", oldCatID));
                ExecuteNonQuery(string.Format("UPDATE blog_Category SET cat_articleCount ={0}  WHERE cat_id={1}", oldcatCounter, oldCatID));
            }

            int catCounter = ExecuteScalar(string.Format("select count(log_id) from blog_Article WHERE log_catid={0}", catID));
            int userCount = ExecuteScalar(string.Format("select count(log_id) from blog_Article WHERE log_authorID={0}", authorID));
            int artCount = ExecuteScalar("select count(log_id) from blog_Article");


            ExecuteNonQuery(string.Format("UPDATE blog_Category SET cat_articleCount ={0}  WHERE cat_id={1}", catCounter, catID));
            ExecuteNonQuery(string.Format("UPDATE blog_User SET user_articleCount ={0}  WHERE user_id={1}", userCount, catID));
            ExecuteNonQuery(string.Format("UPDATE blog_Settings SET set_value0 ={0}  WHERE set_name='counterArticle'", artCount));
        }

        public void UpdateArticleCount(int logID,int catID)
        {
            Article art = GetArticleByID(logID);
            if (art.CategoryID != catID)
            {
                int oldcatCounter = ExecuteScalar(string.Format("select count(log_id) from blog_Article WHERE log_catid={0}", art.CategoryID));
                ExecuteNonQuery(string.Format("UPDATE blog_Category SET cat_articleCount ={0}  WHERE cat_id={1}",oldcatCounter,art.CategoryID));
            }

            int catCounter = ExecuteScalar(string.Format("select count(log_id) from blog_Article WHERE log_catid={0}", catID));
            int userCount = ExecuteScalar(string.Format("select count(log_id) from blog_Article WHERE log_authorID={0}", art.AuthorID));
            int artCount = ExecuteScalar("select count(log_id) from blog_Article");


            ExecuteNonQuery(string.Format("UPDATE blog_Category SET cat_articleCount ={0}  WHERE cat_id={1}", catCounter, catID));
            ExecuteNonQuery(string.Format("UPDATE blog_User SET user_articleCount ={0}  WHERE user_id={1}", userCount, catID));
            ExecuteNonQuery(string.Format("UPDATE blog_Settings SET set_value0 ={0}  WHERE set_name='counterArticle'", artCount));
        }

        public override void UpdateArticleViewCount(int articleId)
        {
            ExecuteNonQuery(string.Format("update blog_article set log_viewcount=log_viewcount+1 where log_id={0}", articleId));
        }

        public override void DeleteArticle(int articleID)
        {
            ExecuteNonQuery(string.Format("DELETE FROM blog_comment where log_id={0}", articleID));
            ExecuteNonQuery(string.Format("DELETE FROM blog_Trackback where log_id={0}", articleID));
            ExecuteNonQuery(string.Format("DELETE FROM blog_Tag where log_ID={0}", articleID));
            


            string strSQL = string.Format("SELECT log_catid,log_authorID FROM blog_article WHERE log_id={0}", articleID);
            int catID=0, authorID=0;
            using (IDataReader dataReader = this.ExecuteReader(strSQL))
            {
                if (dataReader.Read())
                {
                    catID = Convert.ToInt32(dataReader["log_catid"].ToString());
                    authorID = Convert.ToInt32(dataReader["log_authorID"].ToString());
                    
                }
            }
            ExecuteNonQuery(string.Format("DELETE FROM blog_Article WHERE [log_id] = {0}", articleID));
            UpdateArticleCount(catID, catID, authorID);
        }

        public override int GetArticleCategoryCount(int catID)
        {
            string strSql = "SELECT COUNT(log_id) FROM blog_Article";
            if (catID > 0)
            {
                strSql += " WHERE log_catID=?";
            }
            DbCommand dbCommand = db.GetSqlStringCommand(strSql);
            db.AddInParameter(dbCommand, "catID", DbType.Int32, catID);
            return Convert.ToInt32(db.ExecuteScalar(dbCommand));
        }

        public override IPagedCollection<Article> GetPagedArticleByMonth(int startIndex, int pageSize, int year, int month)
        {
            string strPageSQL = string.Format(@"select top {0} cat.*,cat.cat_name as cat_Name,art.* FROM blog_Article art
            left join blog_category cat on art.log_catid=cat.cat_id where log_id<=(select min(log_id) 
            from (select top {1} log_id from blog_Article  where Year(log_postTime) = {2}
	        AND Month(log_postTime) = {3} order by log_id desc) as T) 
            and Year(log_postTime) = {2}
	        AND Month(log_postTime) = {3}
            order by log_id desc;
            ", pageSize, startIndex, year, month);

            string strCountSQL = @"SELECT COUNT(log_id) AS TotalRecords from blog_Article WHERE  Year(log_postTime) = ?
	        AND Month(log_postTime) = ?";
            DbCommand dbCommand = db.GetSqlStringCommand(strCountSQL);
            db.AddInParameter(dbCommand, "year", DbType.Int32, year);
            db.AddInParameter(dbCommand, "month", DbType.Int32, month);
            int count = (int)db.ExecuteScalar(dbCommand);
            IPagedCollection<Article> lists = new PagedCollection<Article>();
            lists.MaxItems = count;
            dbCommand = db.GetSqlStringCommand(strPageSQL);
            using (IDataReader dataReader = db.ExecuteReader(dbCommand))
            {
                while (dataReader.Read())
                {
                    Article article = CommonDataProvider.PopulateArticleFromIDataReader(dataReader);

                    lists.Add(article);
                }

                return lists;
            }
        }

        public override IPagedCollection<Article> GetPagedArticleByYear(int startIndex, int pageSize, int year)
        {

            string strPageSQL = string.Format(@"select top {0} cat.*,cat.cat_name as cat_Name,art.* FROM blog_Article art
            left join blog_category cat on art.log_catid=cat.cat_id where log_id<=(select min(log_id) 
            from (select top {1} log_id from blog_Article where Year(log_postTime) = {2} order by log_id desc) as T) 
            and Year(log_postTime) = {2}
            order by log_id desc;
            ", pageSize, startIndex, year);

            string strCountSQL = @"SELECT COUNT(log_id) AS TotalRecords from blog_Article WHERE  Year(log_postTime) = ?";
            DbCommand dbCommand = db.GetSqlStringCommand(strCountSQL);
            db.AddInParameter(dbCommand, "year", DbType.Int32, year);
            
            int count = (int)db.ExecuteScalar(dbCommand);
            IPagedCollection<Article> lists = new PagedCollection<Article>();
            lists.MaxItems = count;
            dbCommand = db.GetSqlStringCommand(strPageSQL);
            using (IDataReader dataReader = db.ExecuteReader(dbCommand))
            {
                while (dataReader.Read())
                {
                    Article article = CommonDataProvider.PopulateArticleFromIDataReader(dataReader);

                    lists.Add(article);
                }

                return lists;
            }
        }

        public override IPagedCollection<Article> GetPagedArticleByDay(int startIndex, int pageSize, DateTime date)
        {

            string strPageSQL = string.Format(@"select top {0} cat.*,cat.cat_name as cat_Name,art.* FROM blog_Article art
            left join blog_category cat on art.log_catid=cat.cat_id where log_id<=(select min(log_id) 
            from (select top {1} log_id from blog_Article where Year(log_postTime) = Year({2}) 
	        AND Month(log_postTime) = Month({2})
            AND Day(log_postTime) = Day({2}) order by log_id desc) as T) 
            and  Year(log_postTime) = Year({2}) 
	        AND Month(log_postTime) = Month({2})
            AND Day(log_postTime) = Day({2})
            order by log_id desc;
            ", pageSize, startIndex, date);

            string strCountSQL = @"SELECT COUNT(log_id) AS TotalRecords from blog_Article 
            WHERE  
            Year(log_postTime) = Year({0}) 
	        AND Month(log_postTime) = Month({0})
            AND Day(log_postTime) = Day({0})";
            DbCommand dbCommand = db.GetSqlStringCommand(string.Format(strCountSQL,date));
            int count = (int)db.ExecuteScalar(dbCommand);
            IPagedCollection<Article> lists = new PagedCollection<Article>();
            lists.MaxItems = count;
            dbCommand = db.GetSqlStringCommand(strPageSQL);
            using (IDataReader dataReader = db.ExecuteReader(dbCommand))
            {
                while (dataReader.Read())
                {
                    Article article = CommonDataProvider.PopulateArticleFromIDataReader(dataReader);

                    lists.Add(article);
                }

                return lists;
            }
        }

        public override IList<Archive> GetArticleMonthArchive()
        {

            DbCommand dbCommand = db.GetSqlStringCommand(@"SELECT Month(log_posttime) AS [Month]
	            , Year(log_posttime) AS [Year]
	            , 1 AS [Day],
               Count(log_id) AS [Count] 
            FROM [blog_article] 
            GROUP BY Year(log_posttime), Month(log_posttime)
            ORDER BY Year(log_posttime) DESC, Month(log_posttime) DESC");
            
            IList<Archive> lists = new List<Archive>();
            using (IDataReader dataReader = db.ExecuteReader(dbCommand))
            {
                while (dataReader.Read())
                {
                    Archive archive = CommonDataProvider.PopulateArchiveFromIDataReader(dataReader);
                    lists.Add(archive);
                }
            }
            return lists;
        }

        public override IList<Article> GetAllArticleByMonth(int year, int month)
        {
            
            DbCommand dbCommand = db.GetSqlStringCommand("GetAllArticleByMonth");
            db.AddInParameter(dbCommand, "year", DbType.Int32, year);
            db.AddInParameter(dbCommand, "month", DbType.Int32, month);
            IList<Article> lists = new List<Article>();
            using (IDataReader dataReader = db.ExecuteReader(dbCommand))
            {
                while (dataReader.Read())
                {
                    Article article = CommonDataProvider.PopulateNoCatArticleFromIDataReader(dataReader);
                    lists.Add(article);
                }
            }
            return lists;
        }

        public override IList<ArticleLink> GetPreviousAndNextArticle(int articleID)
        {
   
         
            DbCommand dbCommand = db.GetSqlStringCommand(string.Format(@"SELECT log_postTime,log_catID
            FROM [blog_Article]
            WHERE log_id = {0}",articleID));
            DateTime logPostTime = DateTime.Now;
            int logCatID = 0;
            using (IDataReader dr = db.ExecuteReader(dbCommand))
            {
                if(dr.Read())
                {
                    logPostTime =Convert.ToDateTime(dr["log_postTime"].ToString());
                    logCatID = Convert.ToInt32(dr["log_catID"].ToString());
                }
            }
            IList<ArticleLink> lists = new List<ArticleLink>();
            Category cat = GetCategoryByID(logCatID);
            ArticleLink link0 = new ArticleLink();
            link0.PostTime = logPostTime;
            link0.Id = cat.CategoryID;
            link0.Title = cat.Name;
            lists.Add(link0);

            dbCommand = db.GetSqlStringCommand(string.Format(@"
            SELECT Top 1 log_id,log_title,log_posttime
		    FROM blog_Article
	        WHERE log_postTime < ?
		    ORDER BY log_posttime DESC", articleID, logPostTime));
            db.AddInParameter(dbCommand, "log_postTime", DbType.DateTime, logPostTime);
            db.AddInParameter(dbCommand, "logid", DbType.Int32, logCatID);
            using (IDataReader dr = db.ExecuteReader(dbCommand))
            {
                if (dr.Read())
                {
                    ArticleLink link1 = new ArticleLink();
                    link1.PostTime = Convert.ToDateTime(dr["log_postTime"].ToString());
                    link1.Id = Convert.ToInt32(dr["log_id"].ToString());
                    link1.Title = dr["log_title"].ToString();
                    lists.Add(link1);
                }
            }

            dbCommand = db.GetSqlStringCommand(string.Format(@"
            SELECT log_id,log_title,log_posttime FROM
            blog_Article
	            WHERE log_postTime > ?
		            ORDER BY log_posttime ASC
            ", articleID, logPostTime));
            db.AddInParameter(dbCommand, "log_postTime", DbType.DateTime, logPostTime);
            db.AddInParameter(dbCommand, "logid", DbType.Int32, logCatID);
            using (IDataReader dr = db.ExecuteReader(dbCommand))
            {
                if (dr.Read())
                {
                    ArticleLink link2 = new ArticleLink();
                    link2.PostTime = Convert.ToDateTime(dr["log_postTime"].ToString());
                    link2.Id = Convert.ToInt32(dr["log_id"].ToString());
                    link2.Title = dr["log_title"].ToString();
                    lists.Add(link2);
                }
            }

            return lists;
        }

        public override IPagedCollection<Article> GetArticleByUserID(int startIndex, int pageSize, int userID)
        {
            string strPageSQL = string.Format(@"select top {0} cat.*,cat.cat_name as cat_Name,art.* FROM blog_Article art
            left join blog_category cat on art.log_catid=cat.cat_id where log_id<=(select min(log_id) 
            from (select top {1} log_id from blog_Article where log_authorID={2} order by log_id desc) as T) and log_authorID={2}  order by log_id desc;
            ", pageSize, startIndex, userID);

            string strCountSQL = "SELECT COUNT(log_id) AS TotalRecords from blog_Article WHERE log_authorID=?";
            DbCommand dbCommand = db.GetSqlStringCommand(strCountSQL);
            db.AddInParameter(dbCommand, "log_authorID", DbType.Int32, userID);
            int count = (int)db.ExecuteScalar(dbCommand);
            IPagedCollection<Article> lists = new PagedCollection<Article>();
            lists.MaxItems = count;
            dbCommand = db.GetSqlStringCommand(strPageSQL);
            using (IDataReader dataReader = db.ExecuteReader(dbCommand))
            {
                while (dataReader.Read())
                {
                    Article article = CommonDataProvider.PopulateArticleFromIDataReader(dataReader);

                    lists.Add(article);
                }

                return lists;
            }
        }

        public override IPagedCollection<Article> GetPagedArticleBySearch(int startIndex, int pageSize, string prefix)
        {
            StringBuilder sb = new StringBuilder();
            string[] prefixArray = prefix.Split(new char[] { ' ' });
            for (int i = 0; i < prefixArray.Length; i++)
            {
                if (i > 0)
                {
                    sb.Append("AND ");
                }
                sb.AppendFormat("log_title LIKE '%{0}%'", prefixArray[i]);
                // OR log_content0 LIKE '%"+func.checkStr(arrKeywords[i])+"%' OR log_content1 LIKE '%"+func.checkStr(arrKeywords[i])+"%')
                sb.AppendFormat(" OR log_content0 LIKE '%{0}%'", prefixArray[i]);
                sb.AppendFormat(" OR log_content1 LIKE '%{0}%'", prefixArray[i]);
            }

            //string sqllink=
            string strPageSQL2 = string.Format(@"select top {0} cat.*,cat.cat_name as cat_Name,art.* FROM blog_Article art
            left join blog_category cat on art.log_catid=cat.cat_id where log_id<=(select min(log_id) 
            from (select top {1} log_id from blog_Article order by log_id desc) as T) AND {2} order by log_id desc;
            ", pageSize, startIndex, sb.ToString());

            Database db = DatabaseFactory.CreateDatabase();
            DbCommand dbCommand = db.GetSqlStringCommand(strPageSQL2);
            IPagedCollection<Article> lists = new PagedCollection<Article>();
            lists.MaxItems = ExecuteScalar(string.Format("SELECT COUNT(log_id) AS TotalRecords from blog_Article where {2}", sb.ToString()));
            using (IDataReader dataReader = db.ExecuteReader(dbCommand))
            {
                while (dataReader.Read())
                {
                    Article article = CommonDataProvider.PopulateTopAndCommentArticleFromIDataReader(dataReader);
                    //article.Category = CommonDataProvider.PopulateTotalRecordsFromIDataReader(dataReader);
                    lists.Add(article);

                }
                return lists;
            }
        }

    }
        #endregion
}
